﻿// Copyright 2006 - Ricardo Stuven (rstuven@gmail.com)
//
// This file is part of MsSqlSpatial.
// MsSqlSpatial is free software; you can redistribute it and/or modify
// it under the terms of the GNU Lesser General Public License as published by
// the Free Software Foundation; either version 2 of the License, or
// (at your option) any later version.
// 
// MsSqlSpatial is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
// GNU Lesser General Public License for more details.

// You should have received a copy of the GNU Lesser General Public License
// along with MsSqlSpatial; if not, write to the Free Software
// Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA 

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using MsSqlSpatial;

public sealed partial class StoredProcedures
{
    private static readonly string[] validGeometryTypes = new[]
                                                              {
                                                                  "GEOMETRY",
                                                                  "GEOMETRYCOLLECTION",
                                                                  "POINT",
                                                                  "MULTIPOINT",
                                                                  "POLYGON",
                                                                  "MULTIPOLYGON",
                                                                  "LINESTRING",
                                                                  "MULTILINESTRING"
                                                              };

    /// <summary>
    /// Adds a geometry column to an existing table of attributes.
    /// </summary>
    /// <param name="schema_name">The name of the table schema.</param>
    /// <param name="table_name"></param>
    /// <param name="column_name"></param>
    /// <param name="srid">The srid must be an integer value reference to an entry in the SPATIAL_REF_SYS table.</param>
    /// <param name="type">The type must be an uppercase string corresponding to the geometry type, eg. ’POLYGON’ or ’MULTILINESTRING’.</param>
    [SqlProcedure]
    public static void AddGeometryColumn(
        [DefaultValue("")] SqlString schema_name,
        SqlString table_name,
        SqlString column_name,
        [DefaultValue(-1)] SqlInt32 srid,
        [DefaultValue("GEOMETRY")] SqlString type)
    {
        string tableName = table_name.Value;
        string columnName = column_name.Value;
        int sridValue = srid.Value;
        string typeValue = type.Value;

        //1.  ensure that an entry for the SRID exists in the SPATIAL_REF_SYS table.
        //2.  add an entry to the GEOMETRY_COLUMNS table that stores the SRID for the geometry column.
        //3.  add the geometry column to the feature table using a SQL ALTER TABLE statement
        //4.  add the Spatial Reference Check Constraint to the feature table

        if (!IsValidGeometryType(typeValue))
        {
            throw new ApplicationException(
                @"Invalid geometry type. Valid ones are:
\n\t" + string.Join("\n\t", validGeometryTypes));
        }

        using (SqlConnection connection = DatabaseHelper.ContextConnection)
        {
            connection.Open();
            SqlTransaction transaction = connection.BeginTransaction();
            try
            {
                using (SqlCommand command = connection.CreateCommand())
                {
                    command.Transaction = transaction;

                    ExecuteCreateMetaTables(command);

                    if (!SpatialRefSysTable.CheckSRIDExists(command, sridValue))
                    {
                        throw new InvalidSridException(sridValue);
                    }

                    string schemaName = DatabaseHelper.CheckSchemaName(command, schema_name);

                    GeometryColumnsTable.ExecuteAddMetaRecord(command, schemaName, tableName, columnName, sridValue,
                                                              typeValue);
                    GeometryTable.ExecuteAddColumn(command, schemaName, tableName, columnName);
                    GeometryTable.ExecuteAddIndexes(command, schemaName, tableName, columnName);
                    GeometryTable.ExecuteAddConstraintSRID(command, schemaName, tableName, columnName, sridValue);
                    GeometryTable.ExecuteAddConstraintGeometryType(command, schemaName, tableName, columnName, typeValue);
                    GeometryTable.ExecuteCreateTrigger(command, schemaName, tableName, columnName);
                    ExecuteAddGeometryQueryFunctions(command, schemaName, tableName, columnName);

                    // If you add more associated objects to the column, please 
                    // verify whether they have to be added to "ImportFromDatasource" method as well.

                    transaction.Commit();
                    SqlContext.Pipe.Send(string.Format(
                                             @"Added geometry column [{0}].[{1}].[{2}] and constraints SRID={3}, GeometryType={4}."
                                             , schemaName, tableName, columnName, sridValue, typeValue));
                }
            }
            catch
            {
                transaction.Rollback();
                throw;
            }
        }
    }

    private static bool IsValidGeometryType(string type)
    {
        return new List<string>(validGeometryTypes).Contains(type.ToUpperInvariant());
    }
} ;